package com.tmsps.ne4spring2.sql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Service;

import com.tmsps.ne4spring2.core.JdbcPersistence;
import com.tmsps.ne4spring2.page.Page;
import com.tmsps.ne4spring2.sql.param.NeParamList;
import com.tmsps.ne4spring2.sql.param.NeParamTools;
import com.tmsps.ne4spring2.util.ChkUtil;

@Service
public class JdbcComponent implements JdbcPersistence {

	@Resource
	protected JdbcTemplate jt;

	/**
	 * 如果第一个结果是 ResultSet 对象，则返回 true；如果第一个结果是更新计数或者没有结果，则返回 false
	 */

	/**
	 * 分页查询
	 */
	@Override
	public List<Map<String, Object>> queryForList(String sql, Page page) {
		return queryForList(sql, null, page);
	}

	@Override
	public List<Map<String, Object>> queryForList(String sql, Object[] vals, Page page) {
		Integer position = sql.toLowerCase().indexOf("from ");
		String sql_count = "select count(*) cnt " + sql.substring(position);
		position = sql_count.toLowerCase().indexOf("order by");
		 if(position > 0){
			 sql_count =  sql_count.substring(0,position);
			 System.err.println("sql_count-->"+sql_count);
		}
		return this.queryForList(sql, sql_count, vals, page);
	}

	public List<Map<String, Object>> queryForList(String sql, String sql_count, Object[] vals, Page page) {

		List<Map<String, Object>> list = jt.queryForList(sql_count, vals);

		long count = 0l;
		if (list.size() > 0) {
			Number num = (Number) list.get(0).get("cnt");
			count = num.longValue();
		}

		page.setRowTotal(count);

		return onlyQueryForList(sql, vals, page);
	}

	private List<Map<String, Object>> onlyQueryForList(final String sql, final Object[] vals, final Page page) {

		int start = (page.getPageNum() - 1) * page.getPageSize() < 0 ? 0 : (page.getPageNum() - 1) * page.getPageSize();
		String sql_end = sql + " limit " + start + "," + page.getPageSize();

		List<Map<String,Object>> list = jt.queryForList(sql_end, vals);
		//hibernate 版本
		
		// List<Map<String, Object>> list = ht.execute(new
		// HibernateCallback<List<Map<String, Object>>>() {
		// @Override
		// public List<Map<String, Object>> doInHibernate(Session session)
		// throws HibernateException {
		// SQLQuery query = session.createSQLQuery(sql);
		// query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		// // 设置值
		// if (ChkUtil.isNotNull(vals)) {
		// for (int i = 0; i < vals.length; i++) {
		// query.setParameter(i, vals[i]);
		// }
		// }
		// query.setFirstResult(page.getNewBeginResult());
		// query.setMaxResults(page.getPageSize());
		// List<Map<String, Object>> list = query.list();
		// return list;
		// }
		// });

		return list;
	}

	/**
	 * 万能分页查询
	 */
	@Override
	public List<Map<String, Object>> queryForListUniversal(final String sql, final Object[] vals, Page page) {

		long count = jt.execute(sql, new PreparedStatementCallback<Long>() {

			@Override
			public Long doInPreparedStatement(PreparedStatement pstmt) throws SQLException, DataAccessException {
				if (ChkUtil.isNotNull(vals)) {

					for (int i = 0; i < vals.length; i++) {
						pstmt.setObject(i + 1, vals[i]);
					}
				}
				ResultSet rs = pstmt.executeQuery();
				rs.last();
				long count = rs.getRow();
				return count;
			}
		});

		page.setRowTotal(count);

		String sql_new = sql;
		return onlyQueryForList(sql_new, vals, page);

	}

	@Override
	public List<Map<String, Object>> queryForList(String sql, Object[] vals, Map<String, String> sort_params, Page page) {
		Integer position = sql.toLowerCase().indexOf("from ");
		String sql_count = "select count(*) cnt " + sql.substring(position);
		//sql_count 去掉order by 及后面的语句
		 position = sql_count.toLowerCase().indexOf("order by");
		 if(position > 0){
			 sql_count =  sql_count.substring(0,position); 
		}
		System.err.println("sql_count-->"+sql_count);
		return this.queryForList(sql, sql_count, vals, sort_params, page);
	}

	@Override
	public List<Map<String, Object>> queryForList(String sql, String sql_count, Object[] vals, Map<String, String> sort_params, Page page) {
		
		List<Map<String, Object>> list = jt.queryForList(sql_count, vals);
		
		long count = 0l;
		if (list.size() > 0) {
			Number num = (Number) list.get(0).get("cnt");
			count = num.longValue();
		}
		// 分页
		page.setRowTotal(count);

		// 排序
		if (ChkUtil.isNotNull(sort_params)) {
			StringBuilder sb = new StringBuilder();
			for (String key : sort_params.keySet()) {
				if ("table".equals(key)) {
					continue;
				}
				String table = sort_params.get("table");
				table = ChkUtil.isNull(table) ? "" : table + ".";
				sb.append(" ").append(table + key).append(" ").append(sort_params.get(key)).append(",");
			}

			String ob = " order by";
			int orderIndex = sql.toLowerCase().indexOf(ob);
			if (orderIndex == -1) {
				sb.deleteCharAt(sb.length() - 1);
				sql += ob + sb.toString();
			} else {
				sql = sql.substring(0,orderIndex);
				sb.deleteCharAt(sb.length() - 1);
				sql += ob + sb.toString();
				//sql = new StringBuilder(sql).insert(orderIndex + ob.length(), sb).toString();
			}
		}// #if
		return onlyQueryForList(sql, vals, page);
	}

	public Map<String, Object> queryForMap(String sql) {
		return queryForMap(sql, null);
	}

	public Map<String, Object> queryForMap(String sql, Object[] vals) {
		List<Map<String, Object>> list = this.onlyQueryForList(sql, vals, new Page());
		return list.size() > 0 ? list.get(0) : new HashMap<String, Object>();

	}
	
	//新版本的查询
	public List<Map<String, Object>> queryForList(String sql, NeParamList params, Map<String, String> sort_params, Page page) {

		String endSql = NeParamTools.handleSql(sql, params);
		System.err.println(endSql +" --> "+params);
		return this.queryForList(endSql, params.getParamValues(), sort_params, page);
	}
	
	public List<Map<String, Object>> queryForList(String sql, NeParamList params) {
		String endSql = NeParamTools.handleSql(sql, params);
		System.err.println(endSql +" --> "+params);
		return jt.queryForList(endSql, params.getParamValues());
	}

}
